package com.acts.main;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.util.Arrays;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.xmlbeans.impl.piccolo.io.FileFormatException;

public class Test {
	private static final String EXTENSION_XLS = "xls";
	private static final String EXTENSION_XLSX = "xlsx";

	/***
	 * <pre>
	 * 取得Workbook对象(xls和xlsx对象不同,不过都是Workbook的实现类)
	 *   xls:HSSFWorkbook
	 *   xlsx：XSSFWorkbook
	 * @param filePath
	 * @return
	 * @throws IOException
	 * </pre>
	 */
	private static Workbook getWorkbook(String filePath) throws IOException {
		Workbook workbook = null;
		InputStream is = new FileInputStream(filePath);
		if (filePath.endsWith(EXTENSION_XLS)) {
			workbook = new HSSFWorkbook(is);
		} else if (filePath.endsWith(EXTENSION_XLSX)) {
			workbook = new XSSFWorkbook(is);
		}
		return workbook;
	}

	/**
	 * 文件检查
	 * 
	 * @param filePath
	 * @throws FileNotFoundException
	 * @throws FileFormatException
	 */
	private static void preReadCheck(String filePath)
			throws FileNotFoundException, FileFormatException {
		// 常规检查
		File file = new File(filePath);
		if (!file.exists()) {
			throw new FileNotFoundException("传入的文件不存在：" + filePath);
		}

		if (!(filePath.endsWith(EXTENSION_XLS) || filePath
				.endsWith(EXTENSION_XLSX))) {
			throw new FileFormatException("传入的文件不是excel");
		}
	}

	/**
	 * 读取excel文件内容
	 * 
	 * @param filePath
	 * @throws FileNotFoundException
	 * @throws FileFormatException
	 */
	public static void readExcel(String filePath) throws Exception {
		// 检查
		preReadCheck(filePath);
		// 获取workbook对象
		Workbook workbook = null;
		OutputStream out = null;
		try {
			workbook = getWorkbook(filePath);
			// 读文件 一个sheet一个sheet地读取
			Sheet sheet = workbook.getSheetAt(0);
			Sheet sheet2 = workbook.getSheetAt(1);

			int firstRowIndex = sheet.getFirstRowNum();
			int lastRowIndex = sheet.getLastRowNum();

			// 读取首行 即,表头
			Row firstRow = sheet.getRow(firstRowIndex);
			Cell zcNum = firstRow.createCell(10);
			// 定义单元格为字符类型，也可以指定为日期类型、数字类型
			zcNum.setCellType(HSSFCell.CELL_TYPE_STRING);
			zcNum.setCellValue("发货数量");
			// 读取数据行
			Integer noIndex = 2;
			Integer numIndex = 6;

			for (int rowIndex = firstRowIndex + 1; rowIndex <= lastRowIndex; rowIndex++) {
				Row currentRow = sheet.getRow(rowIndex);// 当前行
				Cell currentCell = currentRow.getCell(noIndex - 1);// 物料號
				Double needNum = currentRow.getCell(numIndex - 1)
						.getNumericCellValue();// 需要数量
				String currentCellValue = getCellValue(currentCell, true);
				String[] wuliaohaos = currentCellValue.split("[+]");
				Double num = 0.0;
				for (String wlh : wuliaohaos) {
					int firstRowIndex2 = sheet2.getFirstRowNum();
					int lastRowIndex2 = sheet2.getLastRowNum();
					Boolean find = false;
					for (int rowIndex2 = firstRowIndex2 + 1; rowIndex2 <= lastRowIndex2; rowIndex2++) {
						Row currentRow2 = sheet2.getRow(rowIndex2);// 当前行
						Cell currentCell2 = currentRow2.getCell(0);// 当前单元格
						if (getCellValue(currentCell2, true).equals(wlh)) {
							find = true;
							Cell have = currentRow2.getCell(1);// 数值单元格
							Double haveNum = have.getNumericCellValue();
							num = num == 0 ? haveNum : num;
							num = num >= haveNum ? haveNum : num;
							num = num >= needNum ? needNum : num;
							have.setCellValue(haveNum - num);
						}
					}
					if (!find) {
						num = 0.0;
						break;
					}
				}
				Cell wCell = currentRow.createCell(10);// 当前单元格
				wCell.setCellValue(num);
			}
			for (int rowIndex = firstRowIndex + 1; rowIndex <= lastRowIndex; rowIndex++) {
				Row currentRow = sheet.getRow(rowIndex);// 当前行
				Cell needCell = currentRow.getCell(5);// 需要的数量
				Cell haveCell = currentRow.getCell(10);// 库存数量
				if (needCell.getNumericCellValue() > haveCell
						.getNumericCellValue()
						|| haveCell.getNumericCellValue() == 0) {
					sheet.removeRow(currentRow);
				}
			}
			int i = sheet.getLastRowNum();
			Row tempRow;
			while (i > 0) {
				i--;
				tempRow = sheet.getRow(i);
				if (tempRow == null) {
					sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
				}
			}
			out = new FileOutputStream(filePath);
			workbook.write(out);
			System.out.println("66666666666");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 取单元格的值
	 * 
	 * @param cell
	 *            单元格对象
	 * @param treatAsStr
	 *            为true时，当做文本来取值 (取到的是文本，不会把“1”取成“1.0”)
	 * @return
	 */
	private static String getCellValue(Cell cell, boolean treatAsStr) {
		if (cell == null) {
			return "";
		}
		if (treatAsStr) {
			// 虽然excel中设置的都是文本，但是数字文本还被读错，如“1”取成“1.0”
			// 加上下面这句，临时把它当做文本来读取
			cell.setCellType(Cell.CELL_TYPE_STRING);
		}

		if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			return String.valueOf(cell.getNumericCellValue());
		} else {
			return String.valueOf(cell.getStringCellValue());
		}
	}

	public static void main(String[] args) throws Exception {
/*		System.out.println(System.currentTimeMillis());
		String filePath = "C:\\Users\\qdgz\\Desktop\\test.xlsx";
		readExcel(filePath);
		System.out.println(System.currentTimeMillis());
		String [] strs ={"111","222","333"};
		String b = Arrays.toString(strs);
		System.out.println(b.toString());*/
		String fileName = "原始2 (1).zip";
		fileName = URLEncoder.encode(fileName, "UTF-8");
		System.out.println(fileName);
		fileName = URLDecoder.decode(fileName, "UTF-8");
		System.out.println(fileName);
	}
}
